from PySide6.QtWidgets import QWidget,QTableWidgetItem,QHeaderView,QMenu,QAbstractItemView,QMessageBox,QFileDialog
from views.furtherstudy.furtherstudy import Ui_furtherstudy
from views.furtherstudy.addviewchangefurtherstudydialogView import AddViewChangeFurtherStudyView
from views.furtherstudy.model.furtherstudy import FurtherStudy
from views.person.model.person import PersonBaseInfo
from PySide6.QtCore import Qt
from settings.publicvar import engine
from sqlalchemy.orm import Session
import os,shutil,pandas as pd

class FurtherStudyView(QWidget,Ui_furtherstudy):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.furtherstudytable.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.furtherstudytable.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.furtherstudytable.horizontalHeader().setSectionResizeMode(QHeaderView.Interactive)
        self.furtherstudytable.setContextMenuPolicy(Qt.CustomContextMenu)
        self.furtherstudytable.customContextMenuRequested.connect(self.generate_menu)

        self.downloadmodle.clicked.connect(self.downloadmodleFun)
        self.importbtn.clicked.connect(self.importdataFun)
        self.serearchbtn.clicked.connect(self.serearchFun)
        self.sereachedit.editingFinished.connect(self.serearchFun)
        self.addfurtherstudy.clicked.connect(self.addFurtherStudyFun)
        self.load_table_data()

    # 表格结构
    def setTableStructure(self,data):
        self.furtherstudytable.setRowCount(len(data))
        for i,item in enumerate(data):
            idx = QTableWidgetItem(str(item.id))
            self.furtherstudytable.setItem(i,0,idx)
            name = QTableWidgetItem(item.personbase.name)
            self.furtherstudytable.setItem(i,1,name)
            start_date = QTableWidgetItem(str(item.start_date))
            self.furtherstudytable.setItem(i,2, start_date)
            end_date = QTableWidgetItem(str(item.end_date))
            self.furtherstudytable.setItem(i,3, end_date)
            school = QTableWidgetItem(item.school)
            self.furtherstudytable.setItem(i, 4, school)
            mark = QTableWidgetItem(item.mark)
            self.furtherstudytable.setItem(i,5, mark)

    # 数据加载
    def load_table_data(self):
        with Session(engine) as session,session.begin():
            furtherstudys = session.query(FurtherStudy).all()
            self.setTableStructure(furtherstudys)

    # 表格菜单
    def generate_menu(self,pos):
        menu = QMenu()
        viewitem = menu.addAction('查看')
        changeitem = menu.addAction('修改')
        deletitem = menu.addAction('删除')
        action = menu.exec(self.furtherstudytable.mapToGlobal(pos))
        posrow = self.furtherstudytable.currentRow()
        columns = self.furtherstudytable.columnCount()
        data = [self.furtherstudytable.item(posrow, i).text() for i in range(columns)]
        if action == changeitem:
            changeother = AddViewChangeFurtherStudyView('change', data)
            changeother.exec()
        elif action == deletitem:
            delete = AddViewChangeFurtherStudyView('delete', data)
            other_id = int(self.furtherstudytable.item(posrow, 0).text())
            with Session(engine) as session, session.begin():
                session.query(FurtherStudy).filter(FurtherStudy.id == other_id).delete()
            QMessageBox.information(None, '删除成功', '%s删除成功' % self.furtherstudytable.item(posrow, 1).text())
        elif action == viewitem:
            viewother = AddViewChangeFurtherStudyView('view', data)
            viewother.exec()
        self.load_table_data()

    # 添加进修数据
    def addFurtherStudyFun(self):
        furtherstudy = AddViewChangeFurtherStudyView('add','')
        furtherstudy.exec()

    # 下载模板
    def downloadmodleFun(self):
        model_file_path = "state/model/进修信息模板.xlsx"
        model_file_name = os.path.basename(model_file_path)
        save_path, _ = QFileDialog.getSaveFileName(self, '下载模板', model_file_name,
                                                   f"(*.{model_file_name.split('.')[-1]})")
        shutil.copy2(model_file_path, save_path)
        QMessageBox.information(None, '下载成功', '恭喜你下载成功！')

    # 导入数据
    def importdataFun(self):
        url, a = QFileDialog.getOpenFileUrl(self, '导入数据')
        try:
            data = pd.read_excel(url.toLocalFile(), sheet_name=0, header=1,
                                 names=['idx','name','IDcard','start_date','end_date','school','mark'],
                                 dtype={'IDcard': str},
                                 parse_dates=['start_date','end_date']).drop('idx', axis=1)
            data = data.where(data.notna(), ' ')
            data['start_date'] = data['start_date'].replace({pd.NaT: None})
            data['start_date'] = data['start_date'].replace({pd.NaT: None})
            with Session(engine) as session, session.begin():
                qury_exist_personbase_tem = session.query(PersonBaseInfo).filter(
                    PersonBaseInfo.IDcard.in_(data.IDcard.to_list())).all()
                tem = []
                for item in qury_exist_personbase_tem:
                    tem.append((item.IDcard, item.id))
                if len(tem)>0:
                    qury_exist_personbase_tem = tem
                    qury_exist_personbase = [tem[0] for tem in qury_exist_personbase_tem]
                    qury_exist_personbase_id = pd.DataFrame(qury_exist_personbase_tem).rename(columns={1: 'name_id', 0: 'IDcard'})
                    save_person = data[data['IDcard'].isin(qury_exist_personbase)]
                    save_person = pd.merge(qury_exist_personbase_id, save_person, on='IDcard').drop(columns=['name', 'IDcard']).to_dict('records')
                    furtherstudys = [FurtherStudy(**item) for item in save_person ]
                    session.add_all(furtherstudys)
                    QMessageBox.information(None, '提示', '你导入成功%s条，失败%s条，明细如下！' % (len(furtherstudys), len(data) - len(furtherstudys)))
        except:
            QMessageBox.critical(None, '导入失败', '你没有选择文件！')
        self.load_table_data()

    # 搜索框数据
    def serearchFun(self):
        sereachdata = self.sereachedit.text().strip()
        if sereachdata == '':
            QMessageBox.critical(None, '错误', '你没有搜入数据！')
            self.load_table_data()
        else:
            with Session(engine) as session, session.begin():
                sereachName = session.query(PersonBaseInfo).filter(
                    PersonBaseInfo.name.like('%' + sereachdata + '%')).all()
                sereachIDcard = session.query(PersonBaseInfo).filter(
                    PersonBaseInfo.IDcard.like('%' + sereachdata + '%')).all()
                sereachiphone = session.query(PersonBaseInfo).filter(
                    PersonBaseInfo.iphone.like('%' + sereachdata + '%')).all()
                sereachpersonbase_type = session.query(PersonBaseInfo).filter(
                    PersonBaseInfo.person_type.like('%' + sereachdata + '%')).all()
                sereachData = sereachName + sereachIDcard + sereachiphone + sereachpersonbase_type
                others = []
                if sereachData != []:
                    for personbase in sereachData:
                        for other in personbase.furtherstudys:
                            others.append(other)
                    # 数据处理和加载QTable表
                    self.setTableStructure(others)
                else:
                    QMessageBox.critical(None,'没有数据','你搜索数据不存在！')